ca_only_tidy =transpose(fe_data).map(p => ({'name': p.Name,'Latitude': p['Latitude'],'Longitude': p['Longitude'],'Year':newDate(p[' Date of injury resulting in death (month/day/year)']).getFullYear(),'Number of Fatal Encounters':1,'race': p['Race with imputations'],'lea': p['Agency or agencies involved']}));
viewof fe_ca_search = Inputs.search(ca_only_tidy,{placeholder:"Enter a year, race, or police department name",width:900,})
// a function that will return the dataset filtered by whether the PrimSource property is in the list of energy_type values.filtered = combineDf.filter(function(person) {return gender.indexOf(person.sexOrGenderLabel) >=0&& race.indexOf(person.ethnicGroupLabel) >=0})
// create a function that returns a consistent color for an energy sourcecolors =function(source) {return {"coal":"darkgray","male":"orange","female":"green","trans":"yellow","wind":"palegreen" }[source]}
// now map using the filtered and colors functions:Plot.plot({width:688,height:688,projection: ({width, height}) => d3.geoMercator().center([-119,37.4]).scale((1<<18) / (28*Math.PI)).translate([320,320]),marks: [ Plot.geo(counties, { strokeOpacity:0.8 }), Plot.geo(states, { strokeOpacity:0.3 }), Plot.dot(filtered, {x:"Longitude",y:"Latitude",// r: "Total_MW",fill:"orange",fillOpacity:0.2,stroke:"orange",title: (d) =>`Name: ${d.personLabel}`,// href: (d) => d.properties['Agency or agencies involved'],target:"_blank" }) ]})
Source Code
---title: "Visualizations"execute: echo: falseformat: html: toc: false echo: false keep-hidden: false code-tools: true# css: "/assets/dist/css/bootstrap.min.css" ---# All Fatal Encounters```{python}import sysimport pandas as pdfrom pandas_geojson import to_geojsonfrom SPARQLWrapper import SPARQLWrapper, JSON import plotly.express as pxfrom urllib.request import urlopen# from IPython.display import HTML``````{python}sheet_id ="1dKmaV_JiWcG8XBoRgP8b4e9Eopkpgt7FL7nyspvzAsE"sheet_name ="sample_1"url =f"https://docs.google.com/spreadsheets/d/{sheet_id}/gviz/tq?tqx=out:csv&sheet={sheet_name}"df = pd.read_csv(url)df['Age'].fillna('Not in dataset')df['Race with imputations'].fillna('Not in dataset')df_ca_only = df.loc[df['State'] =='CA']ojs_define(fe_data = df_ca_only)``````{ojs}import {us} from"@observablehq/us-geographic-data"countiesFile =FileAttachment("ca_counties_geoverview.json").json()counties = topojson.feature(countiesFile, countiesFile.objects.counties)states = topojson.feature(us, us.objects.states)``````{ojs}ca_only_tidy =transpose(fe_data).map(p => ({'name': p.Name,'Latitude': p['Latitude'],'Longitude': p['Longitude'],'Year':newDate(p[' Date of injury resulting in death (month/day/year)']).getFullYear(),'Number of Fatal Encounters':1,'race': p['Race with imputations'],'lea': p['Agency or agencies involved']}));``````{ojs}viewof fe_ca_search = Inputs.search(ca_only_tidy,{placeholder:"Enter a year, race, or police department name",width:900,})``````{=html}<div class="fe-bar-chart">``````{ojs}Plot.plot({width:1000,height:600,x: {tickFormat:""},y: {tickSpacing:50},color: {legend:true},marks: [ Plot.barY(fe_ca_search, {x:'Year',y:'Number of Fatal Encounters',fill:'race',sort:'race'}), ]})``````{=html}</div>``````{=html}<div class="fe-all-map">``````{ojs}Plot.plot({width:688,height:688,projection: ({width, height}) => d3.geoMercator().center([-119,37.4]).scale((1<<18) / (28*Math.PI)).translate([320,320]),marks: [ Plot.geo(counties, { strokeOpacity:0.8 }), Plot.geo(states, { strokeOpacity:0.3 }), Plot.dot(fe_ca_search, {x:"Longitude",y:"Latitude",// r: "Total_MW",fill:"orange",fillOpacity:0.2,stroke:"orange",title: (d) =>`Name: ${d.name}\n Year: ${d.Year}\n Law Enforcement Agency Involved: ${d.lea}`,// href: (d) => d.properties['Agency or agencies involved'],target:"_blank" }) ]})``````{=html}</div>```# Wikidata Fatal Encounters```{python}# Pull records of fatal encounter victimsendpoint_url ="https://query.wikidata.org/sparql"query ="""SELECT ?person ?personLabel ?placeOfDeathLabel ?sexOrGenderLabel ?ethnicGroupLabel ?dateOfDeathLabel WHERE { ?person wdt:P5008 wd:Q120754096. ?person wdt:P21 ?sexOrGender. ?person wdt:P172 ?ethnicGroup. ?person wdt:P570 ?dateOfDeath. SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }"""def get_results(endpoint_url, query): user_agent ="WDQS-example Python/%s.%s"% (sys.version_info[0], sys.version_info[1])# TODO adjust user agent; see https://w.wiki/CX6 sparql = SPARQLWrapper(endpoint_url, agent=user_agent) sparql.setQuery(query) sparql.setReturnFormat(JSON)return sparql.query().convert()person_results = get_results(endpoint_url, query)["results"]["bindings"]clean_fe_arr = []for result in person_results: last_slash = result["person"]["value"].rindex('/') qid = result["person"]["value"][last_slash +1:] name = result["personLabel"]["value"]# name_and_qid = name + '_' + qid clean_fe_arr.append({'personLabel': name, # 'name_plus_id': name_and_qid, 'qid': qid, 'ethnicGroupLabel': result['ethnicGroupLabel']['value'][:-1], 'sexOrGenderLabel': result['sexOrGenderLabel']['value'],'dateOfDeathLabel': result['dateOfDeathLabel']['value'], })df_people = pd.DataFrame(clean_fe_arr)``````{python}# Pull coordinates and titles of Death Event Entriesendpoint_url ="https://query.wikidata.org/sparql"query ="""SELECT ?person ?personLabel ?coordinates WHERE { ?person p:P5008 ?statement0. ?statement0 (ps:P5008/(wdt:P279*)) wd:Q120754096. ?person p:P585 ?statement_1. ?statement_1 psv:P585 ?statementValue_1. ?statementValue_1 wikibase:timeValue ?P585_1. ?person wdt:P625 ?coordinates. SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }"""def get_results(endpoint_url, query): user_agent ="WDQS-example Python/%s.%s"% (sys.version_info[0], sys.version_info[1])# TODO adjust user agent; see https://w.wiki/CX6 sparql = SPARQLWrapper(endpoint_url, agent=user_agent) sparql.setQuery(query) sparql.setReturnFormat(JSON)return sparql.query().convert()event_results = get_results(endpoint_url, query)["results"]["bindings"]df_coordinates = pd.DataFrame(event_results)lat = []long= []coords = df_coordinates['coordinates'].tolist()for i inrange(len(coords)): space = coords[i]["value"].index(' ')long.append(float(coords[i]["value"][6:space])) lat.append(float(coords[i]["value"][space +1:-1]))# names.append(labels[i]["value"][9:])df_coordinates['Latitude'] = latdf_coordinates['Longitude'] =longdel df_coordinates['coordinates'] # delete old coordinates columndel df_coordinates['person'] # delete old person columndef isolate_person_name(entry): return entry["value"][9:]df_coordinates["personLabel"] = df_coordinates["personLabel"].map(isolate_person_name)# Combine people entries and death event entriescombine_df = pd.merge(df_coordinates, df_people, on='personLabel')# print(combine_df)# geo_json = to_geojson(df=combine_df, lat='Latitude', lon='Longitude',# properties=['personLabel', 'qid', 'ethnicGroupLabel', 'sexOrGenderLabel', 'dateOfDeathLabel'])``````{ojs}//| panel: inputviewof gender = Inputs.checkbox( ["Female","Male","Transgender","Trans Woman"], {"label":"Gender","value": ["Female","Male","Transgender","Trans Woman"], })``````{ojs}//| panel: inputviewof race = Inputs.checkbox( ["African American","Latine","European-American/White","Asian/Pacific Islander","Native American/Alaskan","Race Unspecified"], {"label":"Race/Ethnicity","value": ["African American","Latine","European-American/White","Asian/Pacific Islander","Native American/Alaskan","Race Unspecified"], })``````{python}ojs_define(combineDf = combine_df)``````{ojs}// a function that will return the dataset filtered by whether the PrimSource property is in the list of energy_type values.filtered = combineDf.filter(function(person) {return gender.indexOf(person.sexOrGenderLabel) >=0&& race.indexOf(person.ethnicGroupLabel) >=0})``````{ojs}// create a function that returns a consistent color for an energy sourcecolors =function(source) {return {"coal":"darkgray","male":"orange","female":"green","trans":"yellow","wind":"palegreen" }[source]}``````{=html}<div class="fe-wikidata-map">``````{ojs}// now map using the filtered and colors functions:Plot.plot({width:688,height:688,projection: ({width, height}) => d3.geoMercator().center([-119,37.4]).scale((1<<18) / (28*Math.PI)).translate([320,320]),marks: [ Plot.geo(counties, { strokeOpacity:0.8 }), Plot.geo(states, { strokeOpacity:0.3 }), Plot.dot(filtered, {x:"Longitude",y:"Latitude",// r: "Total_MW",fill:"orange",fillOpacity:0.2,stroke:"orange",title: (d) =>`Name: ${d.personLabel}`,// href: (d) => d.properties['Agency or agencies involved'],target:"_blank" }) ]})``````{=html}</div>```